package com.xmy.study.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.study.entity.ChaPlan;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ChaPlanMapper extends BaseMapper<ChaPlan> {

    @Select({"<script>",
            "SELECT",
            "*,if(brush_count_temp = 0,1,brush_count_temp) as brush_count",
            "<when test='listState == 2'>",// 正确率学员详情，为了不影响其他的合并课次数据，所以改成了2，为1的不适用于正确率
            ",GROUP_CONCAT( lesson_num ) AS LessonNumNot,",
            "SUM(lowest_shutnum) AS lowestShutnumAll,",
            "SUM(right_num) AS rightNumAll,",
            "SUM(amend_num) AS amendNumAll",
            "</when>",
            "FROM",
            "(",
            "SELECT",
            "t01.id,",
            "t01.subjects_id,",
            "t01.school_id,",
            "t01.year_part,",
            "t01.quarter_num,",
            "t02.course_version_id,",
            "t01.grade_id,",
            "t01.teacher_id,",
            "t01.class_type_id,",
            "t01.year_class_id,",
            "t01.student_id,",
            "t01.linkman_phone,",
            "t02.task_course_num AS lesson_num,",
            "t03.is_first,",
            "t03.is_last,",
            "t04.id AS again_brush_id, ",
            "IFNULL( t03.brush_count,0) as brush_count_temp,",
            "IFNULL( t03.brush_count &gt; 0 AND t03.lowest_shutnum &lt;&gt; 0 AND t03.lowest_shutnum IS NOT NULL, 1) AS yet_finish_count, ",// 已完成
            "IFNULL( t03.is_first = 1 AND (( t03.right_num / t03.lowest_shutnum )* 100 &lt; t03.pass_rade ) AND t03.lowest_shutnum &lt;&gt; 0 AND t03.lowest_shutnum IS NOT NULL AND t04.id IS NOT NULL , 1 ) AS yet_rebrush_count,",// 已复刷
            "IFNULL( t03.pass_rade,t02.pass_rade) as pass_rade, ",
            "( CASE WHEN t03.right_num IS NOT NULL AND t03.pass_rade &gt; 0 and (t03.right_num + t03.amend_num ) &gt;= t03.lowest_shutnum AND ( t03.right_num / t03.lowest_shutnum )* 100 &gt;= t03.pass_rade THEN t03.lowest_shutnum ELSE t02.lowest_shutnum END ) AS lowest_shutnum,",
            "IFNULL( t03.right_num, 0 ) AS right_num,",
            "IFNULL( t03.amend_num, 0 ) AS amend_num,",
            "t03.add_date ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,",
            "t3.class_type_id AS class_type_id,",
            "t2.teacher_id,",
            "t5.linkman_phone",
            "FROM",
            "xmy_erp_db.student_grade AS t1",
            "INNER JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id ",
            "INNER JOIN per_student_account AS t3 ON t1.student_id = t3.student_id",
            "INNER JOIN xmy_erp_db.student AS t5 ON t1.student_id = t5.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum}",
            "AND t1.lesson_type = 1",
            "AND t1.deleted = 0 ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.is_last = 1 ",
            "<when test='include!=null and include!=&apos;&apos;'>",
            "<when test='include == 0'>",
            "and t4.id is null ",
            "</when>",
            "<when test='include == 1'>",
            "and t4.id is not null ",
            "</when>",
            "</when>",
            "<when test='isBeforQuarter == 0 and readingType == 1'>",
            "AND t1.reading_status IN (1,5) ",
            "</when>",
            "<when test='isBeforQuarter == 1 and readingType == 1'>",
            "AND t1.reading_status IN (1,5)",
            "</when>",
            "<when test='readingType == 2'>",
            "AND ((t1.reading_status IN (1,5)) or (t1.reading_status in(3,4) and use_course_count > 0)) ",
            "</when>",
            "AND t1.STATUS &lt;&gt; 3 ",
            "AND t1.deleted = 0 ",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and t3.class_type_id IN (${classTypeId})",
            "</when>",
            "GROUP BY",
            "student_id ",
            ") AS t01",
            "INNER JOIN ( SELECT * FROM edu_task WHERE deleted = 0 and task_category_id = ${taskCategoryId} and subjects_id = ${subjectsId} and task_course_num BETWEEN ${lessonNumStart} and ${lessonNum}) AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.class_type_id = t02.class_type_id",
            "AND t01.year_class_id = t02.year_class_id",
            "<when test='(inType != 2)'>",
            "LEFT ",
            "</when>",
            "<when test='(inType == 2)'>",
            "INNER",
            "</when>",
            "JOIN (SELECT",
            "student_id,",
            "add_date,",
            "lesson_num_finish,",
            "year_class_id,",
            "course_version_id,",
            "class_type_id,",
            "year_part,",
            "quarter_num,",
            "IFNULL( lesson_num, 0 ) AS lesson_num,",
            "right_num,",
            "is_last,",
            "is_first,",
            "amend_num, ",
            "lowest_shutnum, ",
            "brush_count, ",
            "pass_rade",
            "FROM",
            "cha_plan ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum}",
            "AND task_category_id = ${taskCategoryId}",
            "and deleted = 0",
            "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
            "AND add_date BETWEEN '${rightNumStartDate}' AND '${rightNumEndDate}' ",
            "AND right_num &gt; 0 ",
            "</when>",
            ") AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.student_id = t03.student_id ",
            "AND t02.year_class_id = t03.year_class_id ",
            "AND t02.class_type_id = t03.class_type_id ",
            "AND t02.course_version_id = t03.course_version_id",
            "<when test='courseVersionId!=null and courseVersionId!=&apos;&apos;'>",
            "and t02.course_version_id = ${courseVersionId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t01.subjects_id = ${subjectsId}",
            "</when>",

            "AND t02.task_course_num = t03.lesson_num",
            "<when test='radeType!=null and radeType == 1 '>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType != 9 and passType != 10 and passType != 11 and passType != 12'>",
            "and t03.is_last = 1",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and (passType == 9 or passType == 10 or passType == 11 or passType == 12 )'>",
            "and t03.is_first = 1",
            "</when>",
            "</when>",
            "<when test='radeType!=null and (radeType == 2 or radeType == 3) '>",
            "and t03.is_first = 1",
            "</when>",

            "LEFT JOIN ( ",
            "SELECT",
            "id,",
            "student_id,",
            "add_date,",
            "lesson_num_finish,",
            "year_class_id,",
            "course_version_id,",
            "class_type_id,",
            "year_part,",
            "quarter_num,",
            "IFNULL( lesson_num, 0 ) AS lesson_num,",
            "right_num,",
            "is_last,",
            "is_first,",
            "amend_num,",
            "pass_rade,",
            "brush_count,",
            "lowest_shutnum",
            "FROM",
            "cha_plan",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND task_category_id = ${taskCategoryId} ",
            "AND deleted = 0",
            ") AS t04 ON t04.year_part = t03.year_part ",
            "AND t04.quarter_num = t03.quarter_num ",
            "AND t04.student_id = t03.student_id ",
            "AND t04.year_class_id = t03.year_class_id ",
            "AND t04.course_version_id = t03.course_version_id ",
            "AND t04.class_type_id = t03.class_type_id ",
            "AND t04.lesson_num = t03.lesson_num ",
            "and t04.is_last = 1 ",
            "and t04.is_first = 0 ",
            ") AS t",
            "where 1=1",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 2'>",// 已通关
            " AND ((( right_num / lowest_shutnum )* 100 &gt;= pass_rade AND ( right_num + amend_num ) &gt;= lowest_shutnum ) OR ( brush_count_temp &gt;= 2 AND ( right_num + amend_num ) &gt;= lowest_shutnum )) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 3'>",// 未通关
            " AND ((brush_count_temp &lt;= 1 AND (right_num / lowest_shutnum )* 100 &lt; pass_rade ) OR ( brush_count_temp &gt;= 2 AND ( right_num + amend_num ) &lt; lowest_shutnum ) OR (( right_num / lowest_shutnum )* 100 &gt;= pass_rade  AND ( right_num + amend_num ) &lt; lowest_shutnum ) ) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 4'>",// 今日已通关
            " AND ((( right_num / lowest_shutnum )* 100 &gt;= pass_rade AND ( right_num + amend_num ) &gt;= lowest_shutnum ) OR ( brush_count_temp &gt;= 2 AND ( right_num + amend_num ) &gt;= lowest_shutnum )) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "and add_date BETWEEN '${passDateStart}' AND '${passDateEnd}'",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 5'>",// 未刷（未完成）
            "and brush_count_temp = 0 AND (right_num =0 and amend_num=0  and lowest_shutnum is not null)",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 6'>", // 通关率——>需复刷
            "AND brush_count_temp &gt;= 1 AND (brush_count_temp &lt; 2 OR (right_num + amend_num &lt; lowest_shutnum)) AND ((( right_num / lowest_shutnum )* 100 &lt; pass_rade) OR ((right_num / lowest_shutnum * 100 &gt;= pass_rade) AND (right_num + amend_num &lt; lowest_shutnum))) AND lowest_shutnum &lt;&gt; 0",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 7'>",// 第一种已完成详情（录了就算已完成）
            "AND brush_count_temp &gt; 0 AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 8'>",// 第一种根据日期已完成详情（录了就算已完成）
            "AND brush_count_temp &gt; 0 AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL",
            "and add_date BETWEEN '${passDateStart}' AND '${passDateEnd}'",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 9'>",// 应复刷详情
            "AND is_first = 1  ",
            "AND (( right_num / lowest_shutnum )* 100 &lt; pass_rade )",
            "AND lowest_shutnum &lt;&gt; 0 ",
            "AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 10'>",// 已复刷详情
            "AND is_first = 1 ",
            "AND (( right_num / lowest_shutnum )* 100 &lt; pass_rade ) ",
            "AND lowest_shutnum &lt;&gt; 0 ",
            "AND lowest_shutnum IS NOT NULL ",
            "AND again_brush_id IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 11'>",// 根据日期已复刷详情
            "AND is_first = 1 ",
            "AND (( right_num / lowest_shutnum )* 100 &lt; pass_rade ) ",
            "AND lowest_shutnum &lt;&gt; 0 ",
            "AND lowest_shutnum IS NOT NULL ",
            "AND again_brush_id IS NOT NULL ",
            "and add_date BETWEEN '${passDateStart}' AND '${passDateEnd}'",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 12'>",// 未复刷
            "AND is_first = 1  ",
            "AND (( right_num / lowest_shutnum )* 100 &lt; pass_rade )",
            "AND lowest_shutnum &lt;&gt; 0 ",
            "AND lowest_shutnum IS NOT NULL ",
            "AND again_brush_id IS NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 13'>",// 应修正
            "AND brush_count_temp &gt;= 1 AND ( right_num + amend_num ) &lt; lowest_shutnum AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 14'>",// 已完成（正确+修正>=应通关）
            "AND brush_count_temp = 1 AND (right_num + amend_num) &gt;= lowest_shutnum AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 15'>",// 今日已完成（正确+修正>=应通关）
            "AND brush_count_temp = 1 AND (right_num + amend_num) &gt;= lowest_shutnum AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "AND add_date BETWEEN '${passDateStart}' AND '${passDateEnd}' ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 16'>",// 需修正（第一次需修正）
            "AND brush_count_temp = 1 AND ( right_num + amend_num ) &lt; lowest_shutnum AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 17'>",// 复刷应通关
            "AND (brush_count_temp &gt;= 1 AND (brush_count_temp &lt; 2 OR ( right_num + amend_num &lt; lowest_shutnum )) AND (((right_num / lowest_shutnum )* 100 &lt; pass_rade ) OR (( right_num / lowest_shutnum * 100 &gt;= pass_rade ) AND ( right_num + amend_num &lt; lowest_shutnum ))) OR brush_count_temp &gt;= 2 ) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 18'>",// 复刷已通关
            "AND (( brush_count_temp &gt;= 2 AND (( right_num + amend_num ) &gt;= lowest_shutnum AND ( right_num / lowest_shutnum )* 100 &gt;= pass_rade ))) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 19'>",// 复刷未通关
            "AND ( brush_count_temp &gt;= 1 AND (( right_num + amend_num ) &lt; lowest_shutnum OR ( right_num / lowest_shutnum )* 100 &lt; pass_rade )) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL ",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 20'>",// 复刷今日已通关
            "AND (( brush_count_temp &gt;= 2 AND (( right_num + amend_num ) &gt;= lowest_shutnum AND ( right_num / lowest_shutnum )* 100 &gt;= pass_rade ))) AND lowest_shutnum &lt;&gt; 0 AND lowest_shutnum IS NOT NULL",
            "AND add_date BETWEEN '${passDateStart}' AND '${passDateEnd}' ",
            "</when>",
            "AND student_id = #{studentId} ",
            "<when test='listState == 1 or listState == 2'>",
            "GROUP BY student_id",
            "</when>",
            "ORDER BY student_id,lesson_num asc",
            "LIMIT 1",
            "</script>"})
    public ChaPlan getPassRadeDetail(
            @Param("yearPart") String yearPart,
            @Param("quarterNum") String quarterNum,
            @Param("lessonNumStart") String lessonNumStart,
            @Param("lessonNum") String lessonNum,
            @Param("classTypeId") String classTypeId,
            @Param("readingType") String readingType,
            @Param("passType") String passType,
            @Param("courseVersionId") Long courseVersionId,
            @Param("passDate") String passDate,
            @Param("passDateStart") String passDateStart,
            @Param("passDateEnd") String passDateEnd,
            @Param("subjectsId") Long subjectsId,
            @Param("isBeforQuarter") String isBeforQuarter,
            @Param("rightNumStartDate") String rightNumStartDate,
            @Param("rightNumEndDate") String rightNumEndDate,
            @Param("radeType") String radeType,
            @Param("listState") String listState,
            @Param("include") String include,
            @Param("inType") String inType,
            @Param("taskCategoryId") Integer taskCategoryId,
            @Param("studentId") Long studentId
    );


}
